package bysj.dao;

import bysj.domain.GraduateProjectCategory;
import bysj.domain.OpenArea;
import bysj.domain.Teacher;
import bysj.service.GraduateProjectCategoryService;
import bysj.service.TeacherService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class OpenAreaDao {
    private static OpenAreaDao openAreaDao = null;


    private OpenAreaDao() {
    }

    public static OpenAreaDao getInstance() {
        if(openAreaDao ==null){
            openAreaDao = new OpenAreaDao();
        }
        return openAreaDao;
    }

    public Collection<OpenArea> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Department对象
        Collection<OpenArea> openAreas = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM openArea");
        int totalNum = CommonDao.getInstance().count(connection,"openArea");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
            //System.out.println(sql_select);
        }
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            //获得result的参数值
            int id = resultSet.getInt("id");
            String area = resultSet.getString("area");
            int graduateProjectCategory_id = resultSet.getInt("graduateProjectCategory_id");
            GraduateProjectCategory graduateProjectCategory = GraduateProjectCategoryService.getInstance().find(graduateProjectCategory_id);
            int teacher_id = resultSet.getInt("teacher_id");
            Teacher teacher = TeacherService.getInstance().find(teacher_id);
            String requirement = resultSet.getString("requirement");
            int positionNum = resultSet.getInt("positionNum");
            int year = resultSet.getInt("year");
            //为department赋值
            OpenArea openArea = new OpenArea(id,area,graduateProjectCategory,teacher,requirement,positionNum,year);
            //将department添加到departments集合里
            openAreas.add(openArea);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement);
        //返回departments集合
        return openAreas;
    }

    public OpenArea find(Integer id,Connection connection) throws SQLException {
        OpenArea openArea = null;
        //创建sql语句
        String openArea_sql = "SELECT * FROM openArea WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt = connection.prepareStatement(openArea_sql);
        //为预编译对象赋值
        pstmt.setInt(1, id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            //获得result的参数值
            String area = resultSet.getString("area");
            int graduateProjectCategory_id = resultSet.getInt("graduateProjectCategory_id");
            GraduateProjectCategory graduateProjectCategory = GraduateProjectCategoryService.getInstance().find(graduateProjectCategory_id);
            int teacher_id = resultSet.getInt("teacher_id");
            Teacher teacher = TeacherService.getInstance().find(teacher_id);
            String requirement = resultSet.getString("requirement");
            int positionNum = resultSet.getInt("positionNum");
            int year = resultSet.getInt("year");
            //为department赋值
            openArea = new OpenArea(id, area,graduateProjectCategory,teacher,requirement,positionNum,year);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt);
        return openArea;
    }

    public boolean update(OpenArea openArea,Connection connection) throws SQLException {
        //准备预编译语句
        String openArea_sql = "update openArea set area=?,graduateProjectCategory_id=?,teacher_id=?,requirement=?,positionNum=?,year=? where id=" + openArea.getId();
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement(openArea_sql);
        //为预编译对象赋值
        preparedStatement.setString(1, openArea.getArea());
        preparedStatement.setInt(2, openArea.getGraduateProjectCategory().getId());
        preparedStatement.setInt(3, openArea.getTeacher().getId());
        preparedStatement.setString(4, openArea.getRequirement());
        preparedStatement.setInt(5, openArea.getPositionNum());
        preparedStatement.setInt(6, openArea.getYear());
        //执行预编译对象的executeUpdate方法，获得修改的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return rowAdd > 0;
    }
    public boolean add(OpenArea openArea,Connection connection) throws SQLException {
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement("Insert into openArea(area,graduateProjectCategory_id,teacher_id,requirement,positionNum,year) Values " + "(?,?,?,?,?,?)");
        //为预编译对象赋值
        preparedStatement.setString(1, openArea.getArea());
        preparedStatement.setInt(2, openArea.getGraduateProjectCategory().getId());
        preparedStatement.setInt(3, openArea.getTeacher().getId());
        preparedStatement.setString(4, openArea.getRequirement());
        preparedStatement.setInt(5, openArea.getPositionNum());
        preparedStatement.setInt(6, openArea.getYear());
        //执行预编译对象的executeUpdate方法，获得增加的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(preparedStatement);
        return rowAdd > 0;
    }

    public boolean delete(Integer id,Connection connection) throws SQLException {
        //创建sql语句
        String degree_sql = "delete from openArea where id=?";
        //在该连接创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(degree_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译对象的executeUpdate方法，获得删除的记录行数
        int affectedRowNum = pstmt.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(pstmt);
        return affectedRowNum > 0;
    }
}
